create database projS3_EnviSurvey
go
use projS3_EnviSurvey
go

create table AdminAccount(
	ad_ID int identity(1,1) not null primary key,
	ad_username nvarchar(50) not null,
	ad_password nvarchar(50) not null,
	ad_fullname nvarchar(100) not null,
	ad_email nvarchar(100),		
)

create table StaffAccount(
	sta_ID int identity(1,1) not null primary key ,
	sta_username nvarchar(50) not null,
	sta_password nvarchar(50) not null,
	sta_fullname nvarchar(100) not null,
	sta_employeenumber nvarchar(10) not null,
	sta_specification nvarchar(300),	
	ad_email nvarchar(100),
	sta_joindate datetime2
)

create table StudentAccount(
	stu_ID int identity(1,1) not null primary key,
	stu_username nvarchar(50) not null,
	stu_password nvarchar(50) not null,
	stu_fullname nvarchar(100) not null,
	stu_studentcode nvarchar(10) not null,
	ad_email nvarchar(100),
	stu_joindate datetime2
)

create table Survey(
	sur_ID int identity(1,1) not null primary key,
	sur_title nvarchar(200) not null,
	sur_content nvarchar(500) not null,
	sur_startdate datetime2,
	sur_finishdate datetime2,
	sta_ID int foreign key references StaffAccount(sta_ID),
	stu_ID int foreign key references StudentAccount(stu_ID)
)

create table Question(
	qu_ID int identity(1,1) not null primary key,
	qu_content nvarchar(1024) not null,	
	sur_ID int foreign key references Survey(sur_ID),
	sta_ID int foreign key references StaffAccount(sta_ID)
)
create table Answer(
	ans_ID int identity(1,1) not null primary key,
	ans_content1 nvarchar(1024),
	ans_content2 nvarchar(1024),
	ans_content3 nvarchar(1024),
	ans_content4 nvarchar(1024),
	qu_ID int foreign key references Question(qu_ID)
)
create table Result(
	res_ID int identity(1,1) not null primary key,
	res_content nvarchar(500) not null,
	qu_ID int foreign key references Question(qu_ID),
	stu_ID int foreign key references StudentAccount(stu_ID),
	sur_ID int foreign key references Survey(sur_ID)
)

create table Record(
	rec_ID int identity(1,1) not null primary key,	
	stu_ID int foreign key references StudentAccount(stu_ID),
	res_ID int foreign key references Result(res_ID)
)

create table FAQs(
	faq_ID int identity(1,1) not null primary key,
	faq_question nvarchar(1024) not null,
	faq_reswer nvarchar(1024) not null,
	ad_ID int foreign key references AdminAccount(ad_ID)
)
select * from AdminAccount
insert into AdminAccount values ('admin','123456','Group3','group3@gmail.com')

select*from StudentAccount
insert into StudentAccount values ('cuongpm','123456','Phung Manh Cuong','cuongpmgc00502','cuongpmgc00502@gmail.com','2012-12-30')

create table insertDate(
date1 datetime2
)
select * from insertDate
insert into insertDate values ('2012-12-30')